﻿Imports System.Data.OleDb
Public Class clsDBUtility

    Private isTrans As Boolean = False
    Private trans As OleDb.OleDbTransaction
    Private mComm As OleDbCommand = Nothing
    Private conn As New OleDbConnection
    Private connstr As String

    Public Sub New(ByVal sSQLIP As String, ByVal sSQLUid As String, ByVal sSQLPwd As String, ByVal CataLog As String)
        connstr = "Provider=SQLOLEDB;Data Source=" & sSQLIP & ";Initial Catalog=" & CataLog & ";Persist Security Info=True;User ID=" & sSQLUid & " ; Password=" & sSQLPwd & ""
        conn = New OleDbConnection(connstr)
    End Sub

    Public Sub New(ByVal sSQLIP As String, ByVal sSQLUid As String, ByVal sSQLPwd As String)
        connstr = "Provider=SQLOLEDB;Data Source=" & sSQLIP & ";Initial Catalog=filenetdb;Persist Security Info=True;User ID=" & sSQLUid & " ; Password=" & sSQLPwd & ""
        conn = New OleDbConnection(connstr)
    End Sub

#Region "Transaction Section "
    ''' <summary>
    ''' Begin Transaction
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function BeginTrans() As String
        Try
            'trans = New OleDb.OleDbTransaction
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            If isTrans = False Then
                trans = conn.BeginTransaction(IsolationLevel.ReadUncommitted)
                isTrans = True
            End If
            Return "0"
        Catch ex As Exception
            Return ex.ToString
        End Try
    End Function

    ''' <summary>
    ''' Commit Transaction
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CommitTrans() As String
        Try
            If isTrans = True Then
                trans.Commit()
                isTrans = False
            End If
            Return "0"
        Catch ex As Exception
            Return ex.ToString
        Finally
            trans = Nothing
            conn.Close()
        End Try
    End Function

    ''' <summary>
    ''' Rollback Transaction
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function RollbackTrans() As String
        Try
            If isTrans = True Then
                trans.Rollback()
                isTrans = False
            End If
            Return "0"
        Catch ex As Exception
            Return ex.ToString
        Finally
            trans = Nothing
            conn.Close()
        End Try
    End Function
#End Region

#Region "Exec Query SQL Statement"
    ''' <summary>
    ''' EXEC SQL Query 
    ''' </summary>
    ''' <param name="sqlString">SQL Command</param>
    ''' <returns>DataTable</returns>
    ''' <remarks></remarks>
    Public Function ExecQuery(ByVal sqlString As String) As DataTable
        Return ExecQueryDS(sqlString).Tables(0)
    End Function

    ''' <summary>
    ''' 取出從startRowIndex開始 maxinumRow 筆資料
    ''' </summary>
    ''' <param name="sqlString">SQL STRING</param>
    ''' <param name="startRowIndex">資料起始位置(從0開始)</param>
    ''' <param name="maximumRows">資料筆數</param>
    ''' <returns>DataTable</returns>
    ''' <remarks></remarks>
    Public Function ExecQuery(ByVal sqlString As String, ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As DataTable
        Return ExecQueryDS(sqlString, startRowIndex, maximumRows).Tables(0)
    End Function

    ''' <summary>
    ''' 取出從startRowIndex開始 maxinumRow 筆資料
    ''' </summary>
    ''' <param name="sqlString">SQL STRING</param>
    ''' <param name="startRowIndex">資料起始位置(從0開始)</param>
    ''' <param name="maximumRows">資料筆數</param>
    ''' <returns>DataSet</returns>
    ''' <remarks></remarks>
    Private Function ExecQueryDS(ByVal sqlString As String, ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As DataSet

        'Dim dt As DataTable = Nothing
        Dim ds As New DataSet
        Try

            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If

            If String.IsNullOrEmpty(sqlString) Then
                Return Nothing
            End If
            Dim da As New SqlClient.SqlDataAdapter(sqlString, connstr)
            'Dim da As New OleDbDataAdapter(sqlString, conn)
            da.SelectCommand.CommandTimeout = 600
            ' Fill the dataset
            da.Fill(ds, startRowIndex, maximumRows, "ExecQueryDS")
            'dt = ds.Tables(0)

        Catch ex As Exception
            If String.IsNullOrEmpty(sqlString) Then
                Throw ex
            End If
        Finally
            If Not conn.State = ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return ds
    End Function

    ''' <summary>
    ''' EXEC SQL Query 
    ''' </summary>
    ''' <param name="sqlString">SQL Command</param>
    ''' <returns>DataSet</returns>
    ''' <remarks></remarks>
    Private Function ExecQueryDS(ByVal sqlString As String) As DataSet

        'Dim dt As DataTable = Nothing
        Dim ds As New DataSet
        Try
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            If mComm Is Nothing Then
                If isTrans Then
                    mComm = New OleDbCommand(sqlString, conn, trans)
                Else
                    mComm = New OleDbCommand(sqlString, conn)
                End If
            Else
                If mComm.Connection Is Nothing Then
                    mComm.Connection = conn
                    If isTrans Then
                        mComm.Transaction = trans
                    End If

                End If
                mComm.CommandText = sqlString
            End If

            If String.IsNullOrEmpty(sqlString) Then
                Return Nothing
            End If

            Dim da As New OleDbDataAdapter(mComm)
            da.SelectCommand.CommandTimeout = 600
            ' Fill the dataset
            da.Fill(ds)
            'dt = ds.Tables(0)

            Return ds

        Catch ex As Exception
            Throw ex
        Finally
            If Not conn.State = ConnectionState.Closed AndAlso isTrans = False Then
                conn.Close()
            End If
        End Try
    End Function
#End Region

#Region "Exec Stroed Procudure "

    ''' <summary>
    ''' EXEC Store Procedure
    ''' </summary>
    ''' <param name="sqlString">Store Procedure statement</param>
    ''' <returns>DataTable</returns>
    ''' <remarks></remarks>
    Public Function ExecStoredProcedure(ByVal sqlString As String) As DataTable
        Dim dt As DataTable = Nothing
        Dim ds As DataSet
        Dim oComm As OleDbCommand
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If

        If isTrans Then
            oComm = New OleDbCommand(sqlString, conn, trans)
        Else
            oComm = New OleDbCommand(sqlString, conn)
        End If
        oComm.CommandText = sqlString
        ds = ExecStoredProcedureDS(oComm)
        If ds Is Nothing OrElse ds.Tables.Count < 1 Then
            Return Nothing
        Else
            Return ds.Tables(0)
        End If

        'Return ExecStoredProcedure_main(sqlString, sParameterName, sParameterValue)
        ' End If
    End Function

    ''' <summary>
    ''' EXEC Store Procedure with only input parameter
    ''' </summary>
    ''' <param name="SPName">Store Procedure statement</param>
    ''' <param name="sParameterName">Store Procedure Parameter Name</param>
    ''' <param name="sParameterValue">Store Procedure Parameter Value</param>
    ''' <returns>Datatable</returns>
    ''' <remarks></remarks>
    Public Function ExecStoredProcedure(ByVal SPName As String, ByVal sParameterName As String(), ByVal sParameterValue As Object()) As DataTable
        Dim ds As DataSet = Nothing
        Dim oComm As OleDbCommand
        Try
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            If isTrans Then
                oComm = New OleDbCommand(SPName, conn, trans)
            Else
                oComm = New OleDbCommand(SPName, conn)
            End If

            ds = ExecStoredProcedureDS(oComm, SPName, sParameterName, sParameterValue)
            If ds Is Nothing OrElse ds.Tables.Count < 1 Then
                Return Nothing
            Else
                Return ds.Tables(0)
            End If

        Catch ex As Exception
            Throw ex
        Finally
            mComm.Dispose()

        End Try
    End Function

    ''' <summary>
    ''' EXEC Store Procedure with output parameter
    ''' </summary>
    ''' <param name="SPName">Store Procedure statement</param>
    ''' <param name="sParameterName">Store Procedure Parameter Name</param>
    ''' <param name="sParameterValue">Store Procedure Parameter Value</param>
    ''' <returns>Datatable</returns>
    ''' <remarks></remarks>
    Public Function ExecStoredProcedure(ByVal SPName As String, ByVal sParameterName As String(), _
                                        ByVal ParameterType() As OleDbType, _
                                        ByVal ParameterSize() As Integer, _
                                        ByVal ParameterDirection() As ParameterDirection, _
                                        ByVal sParameterValue As Object()) As DataTable
        Dim ds As DataSet = Nothing
        Dim oComm As OleDbCommand = Nothing
        Try
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If

            If isTrans Then
                oComm = New OleDbCommand(SPName, conn, trans)
            Else
                oComm = New OleDbCommand(SPName, conn)
            End If
            oComm.CommandType = CommandType.StoredProcedure
            ds = ExecStoredProcedureDS(oComm, SPName, sParameterName, ParameterType, ParameterSize, ParameterDirection, sParameterValue)
            If ds Is Nothing OrElse ds.Tables.Count < 1 Then
                Return Nothing
            Else
                Return ds.Tables(0)
            End If

        Catch ex As Exception
            Throw ex
        Finally
            oComm.Dispose()

        End Try
    End Function

    ''' <summary>
    ''' EXEC Store Procedure no parameter
    ''' </summary>
    ''' <returns>DataSet</returns>
    ''' <remarks></remarks>
    Private Function ExecStoredProcedureDS(ByRef oComm As OleDbCommand) As DataSet
        Dim ds As New DataSet
        Try

            Dim da As New OleDbDataAdapter(oComm)
            da.SelectCommand.CommandTimeout = 600
            ' Fill the dataset
            da.Fill(ds)

        Catch ex As Exception
            Throw ex
            'LogU.Log(MyClass.ToString, "SQL Command = " & sqlString, ex)
        End Try
        Return ds
    End Function

    ''' <summary>
    ''' EXEC Store Procedure with only input parameter
    ''' </summary>
    ''' <param name="sqlString">Store Procedure statement</param>
    ''' <param name="sParameterName">Store Procedure Parameter Name</param>
    ''' <param name="sParameterValue">Store Procedure Parameter Value</param>
    ''' <returns>DataSet</returns>
    ''' <remarks></remarks>
    Private Function ExecStoredProcedureDS(ByRef oComm As OleDbCommand, ByVal sqlString As String, ByVal sParameterName As String(), ByVal sParameterValue As Object()) As DataSet
        Dim ds As New DataSet
        Dim stmt As String = sqlString
        Try
            If sParameterName.Length <> sParameterValue.Length Then
                Throw New Exception("numbers of Parameter name  and numbers of parameter value not the same")
            End If

            For i As Integer = 0 To sParameterName.Length - 1
                oComm.Parameters.Add(New OleDbParameter(sParameterName(i), sParameterValue(i)))
                stmt &= sParameterName(i) & ":" & sParameterValue(i)
            Next

            Dim da As New OleDbDataAdapter()
            da.SelectCommand = oComm
            da.SelectCommand.CommandText = sqlString


            ' Fill the dataset
            da.Fill(ds)
            If ds Is Nothing Then
                Return Nothing
            Else
                Return ds
            End If
        Catch ex As Exception
            Throw ex
        Finally

        End Try
    End Function

    ''' <summary>
    ''' EXEC Store Procedure with Output parameter
    ''' </summary>
    ''' <param name="oComm">OleDB Command</param>
    ''' <param name="sParameterName">Store Procedure Parameter Name</param>
    ''' <param name="ParameterType" >Store Procedure Parameter Type</param>
    ''' <param name="ParameterSize" >Store Procedure Parameter Size</param>
    ''' <param name="ParameterDirection">Store Procedure Parameter Direction(In/Out)</param>
    ''' <param name="sParameterValue">Store Procedure Parameter Value</param>
    ''' <returns>Datatable</returns>
    ''' <remarks></remarks>
    Private Function ExecStoredProcedureDS(ByRef oComm As OleDbCommand, _
                                        ByVal SPName As String, _
                                        ByVal sParameterName As String(), _
                                        ByVal ParameterType() As OleDbType, _
                                        ByVal ParameterSize() As Integer, _
                                        ByVal ParameterDirection() As ParameterDirection, _
                                        ByRef sParameterValue() As Object) As DataSet
        Dim ds As New DataSet
        Dim oPara As OleDb.OleDbParameter
        Dim i As Integer
        Try
            If sParameterName.Length <> sParameterValue.Length OrElse sParameterName.Length <> ParameterDirection.Length Then
                Throw New Exception("numbers of Parameter name  and numbers of parameter value not the same")
            End If
            oComm.CommandType = CommandType.StoredProcedure
            For i = 0 To sParameterName.Length - 1
                oPara = New OleDb.OleDbParameter
                oPara.ParameterName = sParameterName(i)
                oPara.OleDbType = ParameterType(i)
                oPara.Size = ParameterSize(i)
                oPara.Value = sParameterValue(i)
                oPara.Direction = ParameterDirection(i)
                oComm.Parameters.Add(oPara)
            Next

            'Dim da As New OleDbDataAdapter()
            'da.SelectCommand = mComm
            'da.SelectCommand.CommandText = sqlString

            oComm.ExecuteNonQuery()
            For i = 0 To sParameterName.Length - 1
                If ParameterDirection(i) = Data.ParameterDirection.InputOutput OrElse ParameterDirection(i) = Data.ParameterDirection.Output Then
                    sParameterValue(i) = oComm.Parameters(i)
                End If
            Next

            Return Nothing

            ' Fill the dataset
            'da.Fill(ds)
            'If ds Is Nothing Then
            '    Return Nothing
            'Else
            '    Return ds
            'End If
        Catch ex As Exception
            Throw ex
        Finally

        End Try
    End Function

#End Region

    ''' <summary>
    ''' Exec DML SQL Statement (delete/update/insert)
    ''' </summary>
    ''' <param name="sqlString"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ExecUpdate(ByVal sqlString As String) As Integer
        'Dim comm As OleDbCommand = Nothing
        'Dim trans As OleDbTransaction
        Try
            If Not conn.State = ConnectionState.Open Then
                conn.Open()
            End If
            If isTrans Then
                mComm = New OleDbCommand(sqlString, conn, trans)
            Else
                mComm = New OleDbCommand(sqlString, conn)
            End If

            '   trans = comm.Transaction
            '  trans.Begin()
            Return mComm.ExecuteNonQuery()
            ' trans.Commit()
            'trans.Rollback()
        Catch ex As Exception
            Throw ex
        Finally

            If Not conn.State = ConnectionState.Closed AndAlso isTrans = False Then
                If Not mComm Is Nothing Then
                    mComm.Dispose()
                End If
                conn.Close()
            End If
        End Try
    End Function

    Public Sub open()
        If Not conn.State = ConnectionState.Open Then
            conn.Open()
        End If
    End Sub

    Public Sub close()
        If Not conn Is Nothing AndAlso conn.State = ConnectionState.Open Then
            conn.Close()
        End If
    End Sub

End Class
